USE DS2012
GO

IF OBJECT_ID('usp_CarsStatistic_get_1') IS NOT NULL
	DROP PROCEDURE usp_CarsStatistic_get_1
GO
	
CREATE PROCEDURE usp_CarsStatistic_get_1
AS SET NOCOUNT ON
BEGIN
	INSERT INTO CarsStatistic (CarNumber)
		SELECT DISTINCT loc.CarNumber
		FROM Dislocation loc
		LEFT JOIN CarsStatistic ON CarsStatistic.CarNumber = loc.CarNumber
		WHERE CarsStatistic.CarsStatistic_ID IS NULL

	CREATE TABLE #tempLoc(_ID int identity(1,1) PRIMARY KEY
				, CarNumber int NULL
				, StartDate DateTime NULL
				, StationOfOperationID int  NULL
				, ArrivalStationID int  NULL
	            , OperationDate DateTime  NULL)
	INSERT  INTO #tempLoc(CarNumber, OperationDate, StartDate, StationOfOperationID, ArrivalStationID)
		SELECT DISTINCT CarNumber, OperationDate, StartDate, StationOfOperationID, ArrivalStationID
		FROM (SELECT  loc.CarNumber
	                , loc.OperationDate
					, loc.StartDate
					, loc.StationOfOperationID
					, loc.ArrivalStationID
				FROM CarsStatistic c
				INNER JOIN Dislocation loc ON c.CarNumber = loc.CarNumber) as first_loc
		ORDER BY 1, 2
	
	;WITH buf (MaxID,CarNumber) AS(SELECT MAX(_ID),CarNumber 
									FROM #tempLoc
									GROUP BY CarNumber)
	SELECT	 cur.CarNumber
		, cur.StationOfOperationID as nxt_StationOfOperationID
		, prv.StationOfOperationID as prv_StationOfOperationID
		, cur.ArrivalStationID as ArrivalStationID
	    , cur.OperationDate as nxt_OperationDate
	    , prv.OperationDate as prv_OperationDate
		, cur._ID as nxt_ID
		, prv._ID as prv_ID
		, prv.StartDate as StartDate
		, 0 as isArrived
		, 0 as isIdle
	INTO #change_st
	FROM #tempLoc cur
	LEFT JOIN buf ON buf.CarNumber = cur.CarNumber
	INNER JOIN #tempLoc prv ON cur._ID = prv._ID + 1
							AND cur.CarNumber = prv.CarNumber
							AND (cur.StationOfOperationID <> prv.StationOfOperationID
								OR cur._ID = buf.MaxID)
				
	UPDATE cng
	SET
		cng.prv_ID = loc._ID
		,cng.prv_OperationDate = loc.OperationDate
	FROM #change_st cng
	INNER JOIN #tempLoc loc	ON cng.CarNumber = loc.CarNumber
							AND cng.prv_ID = loc._ID + 1
							AND cng.prv_StationOfOperationID = loc.StationOfOperationID
	WHILE @@ROWCOUNT > 0
		UPDATE cng
		SET
			cng.prv_ID = loc._ID
			,cng.prv_OperationDate = loc.OperationDate
		FROM #change_st cng
		INNER JOIN #tempLoc loc ON cng.CarNumber = loc.CarNumber
								AND cng.prv_ID = loc._ID + 1
								AND cng.prv_StationOfOperationID = loc.StationOfOperationID

	;WITH buf (MaxID,CarNumber) AS(SELECT MAX(_ID),CarNumber 
									FROM #tempLoc
									GROUP BY CarNumber)
	UPDATE cng
	SET 
		cng.isIdle = 1
	FROM #change_st cng
	LEFT JOIN buf ON buf.CarNumber = cng.CarNumber
	WHERE cng.nxt_ID - cng.prv_ID > CASE WHEN cng.nxt_ID = buf.MaxID THEN 0 ELSE 1 END
	
	UPDATE cng 
	SET 
		cng.isArrived = 1
	FROM #change_st cng
	WHERE cng.nxt_StationOfOperationID = cng.ArrivalStationID 
	
	;WITH buf (MaxID,CarNumber) AS(SELECT MAX(_ID),CarNumber 
									FROM #tempLoc
									GROUP BY CarNumber)
	INSERT INTO CarIdle (StartDate,EndDate,IdleStation_ID,CarsStatistic_ID)
		SELECT prv_OperationDate,loc.OperationDate,prv_StationOfOperationID,cars.CarsStatistic_ID
		FROM #change_st cng
		LEFT JOIN CarsStatistic cars ON cars.CarNumber = cng.CarNumber
		LEFT JOIN buf ON buf.CarNumber = cng.CarNumber
		LEFT JOIN #tempLoc loc ON loc._ID = cng.nxt_ID - CASE WHEN buf.MaxID = cng.nxt_ID THEN 0 ELSE 1 END
		WHERE cng.isIdle = 1
		
	INSERT INTO CarArrival (ArrivalDate,StartDate,ArrivalStation_ID,CarsStatistic_ID)
		SELECT nxt_OperationDate,StartDate,ArrivalStationID,cars.CarsStatistic_ID
		FROM #change_st cng
		LEFT JOIN CarsStatistic cars ON cars.CarNumber = cng.CarNumber
		WHERE cng.isArrived = 1 AND cng.isIdle <> 1
END
GO